<?php
/**
 * Created by PhpStorm.
 * User: andrei
 * Date: Jul 12, 2012
 * Time: 2:05:55 PM
 * To change this template use File | Settings | File Templates.
 */
 
class Db_Products_MemoryListProducts extends Common {
	protected static $tableName = TAB_MEMORY_LIST_PRODUCTS;
	protected static $dbFields = array(
        'id',
		'mlp_memory_id',
		'mlp_product_id',
		'mlp_theme_id',
		'mlp_version',
		'mlp_name',
		'mlp_edited_by_user',
		'mlp_date',
		'mlp_personalization_date',
		'mlp_ordered',
		'mlp_photo_id',
		'mlp_preview_link',
        'deleted'
	);


	/**
	 * @var Db_Products_MemoryListProducts
	 */
	public $objectArray;

	public $id;
	public $mlp_memory_id;
	public $mlp_product_id;
	public $mlp_theme_id;
	public $mlp_version;
	public $mlp_name;
	public $mlp_edited_by_user;
	public $mlp_date;
	public $mlp_personalization_date;
	public $mlp_ordered;
	public $mlp_photo_id;
	public $mlp_preview_link;
    public $deleted;

    function __construct($value=NULL, $fieldName=NULL) {
        parent::__construct($value, $fieldName);
    }


	/**
	 * @static
	 * @param $memoryProductId
	 * @param $productId
	 * @param string $memoryIdentifier
	 * @return array|bool
	 */
	public static function getProductDataByMemoryListProductId($memoryProductId, $productId, $memoryIdentifier = '') {
		if(!$productId || !$memoryProductId) {
			return false;
		}

		$sql = "SELECT
					`mlp`.`id` as `mlp_id`,
					`mlp`.`mlp_product_id` as `product_id`,
					`mlp`.`mlp_theme_id`,
					`t`.`id` as `template_id`,
					`li`.`id` as `li_id`,
					`mpp`.`id` as `page_id`,
					`p`.*,
					`mpp`.*,
					`s`.*,
					`t`.*,
					`li`.*,
					`mppt`.*,
					`mppi`.*
				FROM ?f AS `mlp`
				INNER JOIN ?f `p` ON `mlp`.`mlp_product_id` = `p`.`id`
				INNER JOIN ?f `mpp` ON `mlp`.`id` = `mpp`.`mpp_memory_product_id`
				INNER JOIN ?f `s` ON `p`.`p_size_id` = `s`.`id`
				INNER JOIN ?f `ml` ON `mlp`.`mlp_memory_id` = `ml`.`id` AND `ml`.`ml_memory_identifier` = ?
				LEFT JOIN ?f `t` ON `mpp`.`mpp_template_id` = `t`.`id` AND `t`.`deleted` = 0
				LEFT JOIN ?f `li` ON `mpp`.`mpp_template_id` = `li`.`li_owner_id` AND `li`.`li_owned_by` = 'template' AND `li`.`deleted` = 0
				LEFT JOIN ?f `mppt` ON `mpp`.`id` = `mppt`.`mppt_memory_product_page_id` AND `li`.`id` = `mppt`.`mppt_layout_item_id` AND `mppt`.`deleted` = 0
				LEFT JOIN ?f `mppi` ON `mpp`.`id` = `mppi`.`mppi_memory_product_page_id` AND `li`.`id` = `mppi`.`mppi_layout_item_id` AND `mppi`.`deleted` = 0
				WHERE `mlp`.`deleted` = 0 AND `p`.`deleted` = 0 AND `mpp`.`deleted` = 0 AND `mlp`.`id` = ?d AND `mlp`.`mlp_product_id` = ?d ORDER BY `mpp`.`mpp_page_index` ASC ;";

		 	$productData = self::$DB->q($sql,
            self::$tableName,
            TAB_PRODUCTS,
            TAB_MEMORY_LIST_PRODUCT_PAGES,
            TAB_SIZES,
			TAB_MEMORY_LIST,
			$memoryIdentifier,
            TAB_TEMPLATES,
            TAB_LAYOUT_ITEMS,
			TAB_MEMORY_LIST_PRODUCT_PAGE_TEXTS,
			TAB_MEMORY_LIST_PRODUCT_PAGE_IMAGES,
			$memoryProductId,
			$productId);

		$dataReturn = array();
		while ($data = $productData->fetch_object()) {
			if($data->page_id && $data->li_id) {
				$dataReturn[$data->product_id]['p_photo_gallery_id'] = $data->p_photo_gallery_id;
				$dataReturn[$data->product_id]['s_size_id'] = $data->p_size_id;
				$dataReturn[$data->product_id]['s_face_number'] = $data->s_face_number;
				$dataReturn[$data->product_id]['s_width'] = $data->s_width;
				$dataReturn[$data->product_id]['s_height'] = $data->s_height;
				$dataReturn[$data->product_id]['s_name'] = $data->s_name;
				$dataReturn[$data->product_id]['s_orientation'] = $data->s_orientation;
				$dataReturn[$data->product_id]['p_type'] = $data->p_type;
				$dataReturn[$data->product_id]['p_cover_type_id'] = $data->p_cover_type_id;
				$dataReturn[$data->product_id]['p_name'] = $data->p_name;
				$dataReturn[$data->product_id]['p_theme_id'] = $data->mlp_theme_id;
				$dataReturn[$data->product_id]['p_fonts'] = $data->p_fonts;
				$dataReturn[$data->product_id]['pages'][$data->page_id]['pp_template_id'] = $data->mpp_template_id;
				$dataReturn[$data->product_id]['pages'][$data->page_id]['pp_page_index'] = $data->mpp_page_index;
				$dataReturn[$data->product_id]['pages'][$data->page_id]['items'][$data->li_id] = $data;
			}
 		//	$dataReturn[] = $data;
		}
		return $dataReturn;
	}

	/**
	 * @static
	 * @param $productId
	 * @param $themeId
	 * @return bool|mixed
	 */
	public static function checkIsPersonalized($productId, $themeId) {
		if(!$productId || !$themeId) {
			return false;
		}
		return self::$DB->selectRow("SELECT `mlp`.`id` AS `mlp_id`, `mpp`.`mpp_memory_product_id` FROM ?f `mlp`
											LEFT JOIN ?f `mpp` ON `mlp`.`id` = `mpp`.`mpp_memory_product_id` AND `mpp`.`deleted` = 0
											WHERE `mlp`.`mlp_product_id` = ?d AND `mlp`.`mlp_theme_id` = ?d AND `mlp`.`deleted` = 0
											ORDER BY `mpp`.`mpp_memory_product_id` DESC LIMIT 1	;",
											self::$tableName, TAB_MEMORY_LIST_PRODUCT_PAGES, $productId, $themeId);
	}

	/**
	 * @static
	 * @param $limit
	 * @param $sqlSearch
	 * @param $orderBy
	 * @return mixed
	 */
	public static function getMemoryListProducts($limit, $sqlSearch, $orderBy) {
		if (empty($sqlSearch)) {
			$sqlSearch = '1=1';
			$joinSearch = '';
		} else {
			$joinSearch = " LEFT JOIN " . TAB_TRANSLATIONS . " tr ON tr.t_module = p.p_module AND tr.t_key = p.p_name AND tr.t_lang_id = " . conf('COUNTRY_LANG_ID') ;
			$joinSearch .= " LEFT JOIN " . TAB_TRANSLATIONS . " tr2 ON tr2.t_module = te.te_module AND tr2.t_key = te.te_name AND tr2.t_lang_id = " . conf('COUNTRY_LANG_ID') ;

			$sqlSearch .= " OR (`tr`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
			$sqlSearch .= " OR (`tr2`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
		}
		$orderBy = (empty($orderBy) ? ' ORDER BY `mlp`.`id` ASC ' : $orderBy);

		$sql = "SELECT `mlp`.*, `p`.`p_name`, `mlp`.`id` AS `mlp_id`, `te`.`te_name` FROM ?f AS `mlp`
				LEFT JOIN ?f AS `p` ON `mlp`.`mlp_product_id` = `p`.`id` AND `p`.`deleted` = 0
				LEFT JOIN ?f AS `te` ON `te`.`id` = `mlp`.`mlp_theme_id` AND `te`.`deleted` = 0
				{$joinSearch}
				WHERE `mlp`.`deleted` = 0 AND {$sqlSearch} {$orderBy} {$limit};";

		return self::$DB->select($sql, self::$tableName, TAB_PRODUCTS, TAB_THEMES);
	}

	/**
	 * @static
	 * @param string $sqlCondition
	 * @return mixed
	 */
	public static function getTotalMemoryListProducts($sqlCondition = '') {

		if (empty($sqlCondition)) {
			$sqlCondition = '1=1';
			$joinSearch = '';
		} else {
			$joinSearch = " LEFT JOIN " . TAB_TRANSLATIONS . " tr ON tr.t_module = p.p_module AND tr.t_key = p.p_name AND tr.t_lang_id = " . conf('COUNTRY_LANG_ID') ;
			$joinSearch .= " LEFT JOIN " . TAB_TRANSLATIONS . " tr2 ON tr2.t_module = te.te_module AND tr2.t_key = te.te_name AND tr2.t_lang_id = " . conf('COUNTRY_LANG_ID') ;

			$sqlCondition .= " OR (`tr`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
			$sqlCondition .= " OR (`tr2`.`t_content` LIKE " . self::$DB->escapeValue('%' . str_replace('%', "\\%", post('sSearch')) . '%') . ")";
		}

		$sql = "SELECT COUNT(DISTINCT `mlp`.`id`) FROM ?f AS `mlp`
				LEFT JOIN ?f AS `p` ON `mlp`.`mlp_product_id` = `p`.`id` AND `p`.`deleted` = 0
				LEFT JOIN ?f AS `te` ON `te`.`id` = `mlp`.`mlp_theme_id` AND `te`.`deleted` = 0
				{$joinSearch}
				WHERE `mlp`.`deleted` = 0 AND {$sqlCondition} ;";

		return self::$DB->selectCell($sql, self::$tableName, TAB_PRODUCTS, TAB_THEMES);
	}

	/**
	 * @static
	 * @param $memoryIdentifier
	 * @param $productId
	 * @return bool|mixed
	 */
	public static function getMemoryProductIdByIdentifier($memoryIdentifier, $productId) {
		if (!$memoryIdentifier || !$productId) {
			return false;
		}
		$sql = "SELECT mlp.id FROM ?f mlp
				INNER JOIN ?f ml ON mlp.mlp_memory_id = ml.id
				WHERE mlp.mlp_product_id = ?d AND ml.ml_memory_identifier = ? AND ml.deleted = 0 AND mlp.deleted = 0
				ORDER BY mlp.mlp_date DESC
		";
		return self::$DB->selectCell($sql, self::$tableName, TAB_MEMORY_LIST, $productId, $memoryIdentifier);
	}

	public static function getAllProductsByMemoryIdentifier($memoryIdentifier) {
		if (!$memoryIdentifier) {
			return false;
		}
		$sql = "SELECT mlp.id as mlp_id,mlp.mlp_version, mlp.mlp_name, mlp.mlp_date, ml.ml_memory_identifier,mlp.mlp_personalization_date, s.s_width, s.s_height,pd.id as product_id, pd.p_name, c.c_color_rgb, pt.pt_theme_id,pt.pt_is_default, ph.*,mpp.mpp_memory_product_id FROM ?f mlp
				INNER JOIN ?f ml ON mlp.mlp_memory_id = ml.id
				INNER JOIN ?f pd ON mlp.mlp_product_id = pd.id AND pd.deleted = 0
				INNER JOIN ?f s ON pd.p_size_id = s.id AND s.deleted = 0
				LEFT JOIN (
					SELECT mpp_memory_product_id FROM ?f WHERE deleted = 0 GROUP BY mpp_memory_product_id
				) as mpp ON mlp.id = mpp.mpp_memory_product_id
				LEFT JOIN ?f pt ON pd.id = pt.pt_product_id
				LEFT JOIN ?f te ON pt.pt_theme_id = te.id AND te.deleted = 0
				LEFT JOIN ?f c ON te.te_color_id = c.id AND c.deleted = 0
				LEFT JOIN ?f pg ON pd.id = pg.pg_owner_id AND pg.pg_gallery_type = 'product' AND pg.deleted = 0
				LEFT JOIN ?f ph ON pg.id = ph.ph_gallery_id AND ph.deleted = 0 AND pt.pt_photo_id = ph.id
				WHERE mlp.deleted = 0 AND ml.ml_memory_identifier = ? ORDER BY ml.ml_date DESC,pt.pt_is_default DESC
		";
		$result = self::$DB->q($sql, self::$tableName, TAB_MEMORY_LIST, TAB_PRODUCTS, TAB_SIZES, TAB_MEMORY_LIST_PRODUCT_PAGES, TAB_PRODUCT_THEME_PHOTOS, TAB_THEMES, TAB_COLORS, TAB_PHOTO_GALLERIES, TAB_PHOTOS, $memoryIdentifier);
		$dataReturn = array();
		while ($data = $result->fetch_assoc()) {
			$dataReturn[$data['mlp_id']]['mlp_version'] = $data['mlp_version'];
			$dataReturn[$data['mlp_id']]['mlp_name'] = $data['mlp_name'];
			$dataReturn[$data['mlp_id']]['mlp_date'] = $data['mlp_date'];
			$dataReturn[$data['mlp_id']]['ml_memory_identifier'] = $data['ml_memory_identifier'];
			$dataReturn[$data['mlp_id']]['product_id'] = $data['product_id'];
			$dataReturn[$data['mlp_id']]['p_name'] = $data['p_name'];
			$dataReturn[$data['mlp_id']]['s_width'] = $data['s_width'];
			$dataReturn[$data['mlp_id']]['s_height'] = $data['s_height'];
			$dataReturn[$data['mlp_id']]['mlp_personalization_date'] = $data['mlp_personalization_date'];
			$dataReturn[$data['mlp_id']]['mpp_memory_product_id'] = $data['mpp_memory_product_id'];
			$dataReturn[$data['mlp_id']]['themes'][$data['pt_theme_id']] = array(
				'c_color_rgb' => $data['c_color_rgb'],
				'pt_is_default' => $data['pt_is_default'],
				'ph_name' => $data['ph_name'],
				'ph_path' => $data['ph_path'],
				'ph_ow' => $data['ph_ow'],
				'ph_oh' => $data['ph_ow'],
				'ph_resized' => $data['ph_resized'],
				'ph_rh' => $data['ph_rh'],
				'ph_rw' => $data['ph_rw']
			);
		}
		return $dataReturn;
	}

	public static function deleteMemoryList($memoryListId, $productEncodedId) {
		$sql = "UPDATE ?f mlp
				INNER JOIN ?f ml ON `ml`.`id` = `mlp`.`mlp_memory_id` AND `ml`.`deleted` = 0 AND `ml`.`ml_memory_identifier` LIKE ?
				SET `mlp`.`deleted` = 1
				WHERE `mlp`.`deleted` = 0 AND `mlp`.`mlp_encoded_id` LIKE ?
		";
		self::$DB->q($sql, TAB_MEMORY_LIST_PRODUCTS, TAB_MEMORY_LIST, $memoryListId, $productEncodedId);
		return (self::$DB->affectedRows() == 1);
	}

	/**
	 * @static
	 * @param $newMemoryId
	 * @param $oldMemoryId
	 * @return bool|int
	 */
	public static function updateMemoryProductsByMemoryId($newMemoryId, $oldMemoryId) {
		if (!$newMemoryId || !$oldMemoryId) {
			return false;
		}
		$sql = "UPDATE ?f SET mlp_memory_id = ?d WHERE mlp_memory_id = ?d";
		self::$DB->q($sql, self::$tableName, $newMemoryId, $oldMemoryId);
		return self::$DB->affectedRows();
	}

	public static function getMemoryProductsForStep($sessionMemoryProductsList) {
		$sessionMemoryProductsIdList = array();
        if ($sessionMemoryProductsList) {
			foreach ($sessionMemoryProductsList as $mlpId=> $sessionMemoryProduct) {
				$sessionMemoryProductsIdList[] = Utils::memoryProductIdDecode($mlpId);
			}
		} else {
			return false;
		}
		$sql = "SELECT `mlp`.*, `pr`.`p_default_url`, `pr`.`p_name`, `ph`.`ph_resized`,  `ph`.`ph_path`, `ph_prev`.`ph_resized` as `prev_resized`,  `ph_prev`.`ph_path` as `prev_path`,
                                `ppts`.`ppts_paper_type_id`,
                                `ppt`.`ppt_name`,
                                `ppt`.`ppt_name_for_print`,
                                `ppt`.`ppt_description`,
                                `ppt`.`ppt_thumb_url`
                FROM ?f mlp
		        LEFT JOIN ?f pr ON `pr`.`id` = `mlp`.`mlp_product_id`
                LEFT JOIN ?f ppts ON `ppts`.`ppts_size_id` = `pr`.`p_size_id`
                LEFT JOIN ?f ppt ON `ppts`.`ppts_paper_type_id`  = `ppt`.`id`
                LEFT JOIN ?f pt ON `pt`.`pt_product_id` = `mlp`.`mlp_product_id` AND `pt`.`pt_is_default` = 1
                LEFT JOIN ?f ph ON `ph`.`id` = `pt`.`pt_photo_id`
                LEFT JOIN ?f ph_prev ON `ph_prev`.`id` = `mlp`.`mlp_photo_id`
		        WHERE `mlp`.`id`  IN (?) AND `mlp`.`deleted` = 0";
		$result =  self::$DB->q(true, $sql, self::$tableName, TAB_PRODUCTS, TAB_PRODUCTS_PAPER_TYPES_SIZES, TAB_PRODUCTS_PAPER_TYPES, TAB_PRODUCT_THEME_PHOTOS, TAB_PHOTOS, TAB_PHOTOS, $sessionMemoryProductsIdList);
		if (!$result) {
			return false;
		}
		$dataReturn = array();
		while ($memoryProduct = $result->fetch_assoc()) {
			$encodeMemoryProductId =  Utils::memoryProductIdEncode($memoryProduct['id']);
			if (isset($sessionMemoryProductsList[$encodeMemoryProductId]['categoryId']) &&
				isset($sessionMemoryProductsList[$encodeMemoryProductId]['productId']) &&
				isset($sessionMemoryProductsList[$encodeMemoryProductId]['themeId']) &&
				isset($sessionMemoryProductsList[$encodeMemoryProductId]['priceId'])
			) {
				if (!isset($dataReturn[$memoryProduct['id']])) {

					$dataReturn[$memoryProduct['id']] = $memoryProduct;
					if (isset($sessionMemoryProductsList[$encodeMemoryProductId]['categoryId'])) {
						$sessionCategoryId = $sessionMemoryProductsList[$encodeMemoryProductId]['categoryId'];
						$category = Db_Products_Categories::findArrayById($sessionCategoryId);
						$dataReturn[$memoryProduct['id']]['cat_name'] = $category['pc_name'];
						$dataReturn[$memoryProduct['id']]['cat_show_envelope'] = $category['pc_show_envelope'];
					}
					$dataReturn[$memoryProduct['id']]['paper_types'] = array();
				}
				$dataReturn[$memoryProduct['id']]['paper_types'][$memoryProduct['ppts_paper_type_id']] = array(
					'ppt_name' => $memoryProduct['ppt_name'],
					'ppt_name_for_print' => $memoryProduct['ppt_name_for_print'],
					'ppt_description' => $memoryProduct['ppt_description'],
					'ppt_thumb_url' => $memoryProduct['ppt_thumb_url']
				);
			} else {
				self::$S->Get('cart');
				self::$S->Remove($encodeMemoryProductId);
			}
		}
		return $dataReturn;
	}

	/**
	 * @static
	 * @param $memoryProductId
	 * @return array
	 */
	public static function getMemoryProductNames($memoryProductId) {
		if (!$memoryProductId) {
			return array();
		}
		$result = self::$DB->q("SELECT mn_name FROM ?f WHERE mn_memory_product_id = ?d", TAB_MEMORY_LIST_PRODUCT_NAMES, $memoryProductId);
		$dataReturn = array();
		while ($data = $result->fetch_object()) {
			$dataReturn[] = $data->mn_name;
		}
		return $dataReturn;
	}

	/**
	 * @static
	 * @param $memoryProductId
	 * @return bool|mysqli_result
	 */
	public static function deleteNamesByMemoryProductId($memoryProductId) {
		if (!$memoryProductId) {
			return false;
		}
		return self::$DB->q("DELETE FROM ?f WHERE mn_memory_product_id = ?d", TAB_MEMORY_LIST_PRODUCT_NAMES, $memoryProductId);
	}
}


